-- =============================================
-- Request individual content to be published.
--
-- Modifications
-- 07/11/2003  E.Tatsui
-- =============================================

CREATE                          PROCEDURE [dbo].[amsp_CMRequestPublishContent]
  @InContentID numeric,
  @InContactID numeric,
  @OutPublishRequestID numeric OUTPUT
AS
BEGIN

  DECLARE
  @NavContentGroupInd char(1),
  @PublishFrequency int,
  @PublishRequestID numeric,
  @WebsiteKey uniqueidentifier,
  @PreviousWebsiteKey uniqueidentifier,
  @PreFuseURL varchar(255),
  @PostFuseURL varchar(255),
  @NewPreFuseURL varchar(255),
  @NewPostFuseURL varchar(255),
  @NavMenuID numeric,
  @WorkflowStatusCode char(1),
  @HTMLContentID numeric,
  @FileCount numeric,
  @LinkCount numeric
  
  SELECT @NavContentGroupInd = b.NavContentGroupInd,
         @PublishFrequency = a.PublishFrequency,
         @WebsiteKey = b.WebsiteKey,
         @PreviousWebsiteKey = b.PreviousWebsiteKey,
         @PreFuseURL = a.PreFuseURL,
         @PostFuseURL = a.PostFuseURL,
         @NavMenuID = b.NavMenuID,
         @WorkflowStatusCode = a.WorkflowStatusCode,
         @HTMLContentID = ch.ContentID,
         @FileCount = (SELECT COUNT(*) FROM Content_File x WITH (NOLOCK) WHERE x.ContentID = a.ContentID),
         @LinkCount = (SELECT COUNT(*) FROM Content_Link x WITH (NOLOCK) WHERE x.ContentID = a.ContentID)
    FROM Content a WITH (NOLOCK)
    LEFT OUTER JOIN Content_HTML ch WITH (NOLOCK)
      ON a.ContentID = ch.ContentID, Nav_Menu b WITH (NOLOCK)
   WHERE a.NavMenuID = b.NavMenuID
     AND a.ContentID = @InContentID

  INSERT INTO Publish_Request
              (ContactID,
               RequestDateTime)
  VALUES (@InContactID,
          CURRENT_TIMESTAMP)

  -- Make sure PreFuseURL is up to date.
  EXEC amsp_CMGetFuseURL @NavMenuID, @InContentID, @NewPreFuseURL OUTPUT, @NewPostFuseURL OUTPUT

  IF @WorkflowStatusCode IN ('P','A') AND
     (IsNull(@NewPreFuseURL,'') <> IsNull(@PreFuseURL,'')
       OR IsNull(@NewPreFuseURL,'') <> IsNull(@PostFuseURL,'')) BEGIN
    UPDATE Content
       SET PreFuseURL = @NewPreFuseURL,
           PostFuseURL = @NewPostFuseURL
     WHERE ContentID = @InContentID
  END

  SELECT @PublishRequestID = @@Identity

  -- Insert this request as long it's not already there.
  IF @HTMLContentID IS NOT NULL OR @FileCount > 0 OR @LinkCount > 0 BEGIN
    INSERT INTO Publish_Request_Detail
              (PublishRequestID,
               ContentID,
               PublishRegenerateInd,
               PublishServerCode,
               Frequency,
               WebsiteKey)
    SELECT @PublishRequestID,
           @InContentID,
           'P',
           a.PublishServerCode,
           IsNull(@PublishFrequency,0) * 3600,
           @WebsiteKey
      FROM Website a WITH (NOLOCK) LEFT OUTER JOIN Publish_Request_Detail x WITH (NOLOCK)
        ON x.ContentID = @InContentID
       AND (x.PublishRequestStatusCode = 'N' OR x.Frequency > 0)
       AND x.WebsiteKey = a.WebsiteKey
       AND x.PublishRegenerateInd = 'P'
       AND x.PublishRequestStatusCode = 'N'
     WHERE a.WebsiteKey = @WebsiteKey
       AND x.PublishRequestDetailID IS NULL
  END

  IF @PreviousWebsiteKey IS NOT NULL AND @PreviousWebsiteKey <> @WebsiteKey BEGIN
    DELETE FROM Publish_Request_Detail
     WHERE WebsiteKey = @PreviousWebsiteKey
       AND ContentID = @InContentID

    INSERT INTO Publish_Request_Detail
              (PublishRequestID,
               ContentID,
               PublishRegenerateInd,
               PublishServerCode,
               Frequency,
               WebsiteKey,
               DeleteFlag)
    SELECT @PublishRequestID,
           @InContentID,
           'P',
           PublishServerCode,
           0,
           @PreviousWebsiteKey,
           'Y'
      FROM Website a WITH (NOLOCK)
     WHERE WebsiteKey = @PreviousWebsiteKey

  END

  /* Commented out for now. Might be used in future releases.
  ELSE BEGIN -- @NavContentGroupInd = 'C'
    INSERT INTO Publish_Request_Detail
                (PublishRequestID,
                 ContentID,
                 PublishRegenerateInd,
                 PublishServerCode,
                 Frequency)
    SELECT @PublishRequestID,
           @InContentID,
           'P',
           PublishServerCode,
           IsNull(@PublishFrequency,0) * 3600
      FROM Content_Publish_Server a
     WHERE ContentID = @InContentID
       AND NOT EXISTS (SELECT 1
                         FROM Publish_Request_Detail x
                        WHERE ContentID = @InContentID
                          AND (PublishedDateTime IS NULL OR Frequency > 0)
                          AND x.PublishServerCode = a.PublishServerCode
                          AND x.PublishRegenerateInd = 'P')
    
  END  */

  SET @OutPublishRequestID = @PublishRequestID
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMRequestPublishContent] TO [IMIS]
GO